SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

    
CREATE VIEW dbo.DnnProduct_vw_TabPermissions
AS
    SELECT  
		TP.TabPermissionID, 
		T.TabID, 
		P.PermissionID, 
		TP.RoleID, 
		CASE TP.RoleID
			when -1 then 'All Users'
			when -2 then 'Superuser'
			when -3 then 'Unauthenticated Users'
			else 	R.RoleName
		END AS 'RoleName',
		TP.AllowAccess, 
		TP.UserID,
		U.Username,
		U.DisplayName, 
		P.PermissionCode, 
		P.ModuleDefID, 
		P.PermissionKey, 
		P.PermissionName,
		T.PortalId,
		TP.CreatedByUserID, 
		TP.CreatedOnDate,
		TP.LastModifiedByUserID,
		TP.LastModifiedOnDate

	FROM dbo.DnnProduct_TabPermission AS TP 
		INNER JOIN dbo.DnnProduct_Tabs AS T ON TP.TabID = T.TabID	
		LEFT OUTER JOIN dbo.DnnProduct_Permission AS P ON TP.PermissionID = P.PermissionID 
		LEFT OUTER JOIN dbo.DnnProduct_Roles AS R ON TP.RoleID = R.RoleID
		LEFT OUTER JOIN dbo.DnnProduct_Users AS U ON TP.UserID = U.UserID

GO
